package df.bean.process;

/**
 *
 * @author T.
 */
import df.bean.db.conn.DBConnection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ProcessJoinBill {

    private String startDate = "";
    private String toDate = "";
    private String hospital_code = "";
    private DBConnection con;

    private String getStartDate() {
        return startDate;
    }

    private String getToDate() {
        return toDate;
    }

    private void setStartDate(String startDate) {
        this.startDate = startDate;
    }

    private void setToDate(String toDate) {
        this.toDate = toDate;
    }

    private ResultSet getLineNO(){
        String sql = "SELECT " +
                "LINE_NO, COUNT(LINE_NO) " +
                "FROM INT_HIS_BILL " +
                "WHERE " +
                "AMOUNT_BEF_DISCOUNT > 0 " +
                "AND IS_ONWARD = 'N' " +
                "AND HOSPITAL_CODE = '"+this.hospital_code+"' "+
                "AND BILL_DATE BETWEEN '"+this.getStartDate()+"' AND '"+this.getToDate()+"' " +
                "GROUP BY LINE_NO HAVING COUNT(LINE_NO) > 1;";
        DBConnection con = new DBConnection();
        con.connectToLocal();
        ResultSet rs = null;
        try{
            rs = con.executeQuery(sql);
        }catch(Exception err){
            System.out.println(err.getMessage());
        }
        return rs;
    }
    private void getBillByLineNO(String idRef){
        String sql = "SELECT BILL_NO, LINE_NO, AMOUNT_BEF_DISCOUNT, TRANSACTION_TYPE " +
                	 "FROM INT_HIS_BILL WHERE HOSPITAL_CODE = '"+this.hospital_code+"' " +
                	 "AND IS_ONWARD = 'N' AND LINE_NO = '"+ idRef +"'";
        Double temp_sum = 0.0;
        String temp_line = "";
        String temp_bill = "";
        String sql_insert = "";

        DBConnection con = new DBConnection();
        con.connectToLocal();
        ResultSet rs = this.con.executeQuery(sql);
        try{

            int iCount =0;
            while(rs.next()){
                if("INV".equalsIgnoreCase(rs.getString("TRANSACTION_TYPE")) || iCount==0){
                    temp_line = rs.getString("LINE_NO");
                    temp_bill = rs.getString("BILL_NO");
                    iCount++;
                }
                temp_sum = temp_sum + Double.parseDouble(rs.getString("AMOUNT_BEF_DISCOUNT"));                
            }
            sql_insert += "update INT_HIS_BILL SET AMOUNT_BEF_DISCOUNT='0.0' WHERE HOSPITAL_CODE = '"+
            			  this.hospital_code+"' AND IS_ONWARD = 'N' AND LINE_NO='"+ temp_line +"'; ";
            sql_insert += "update INT_HIS_BILL SET AMOUNT_BEF_DISCOUNT='"+ temp_sum +"' WHERE HOSPITAL_CODE = '"+
            			  this.hospital_code+"' AND IS_ONWARD = 'N' AND LINE_NO='"+ temp_line +"' AND BILL_NO='"+ temp_bill +"'";            
            //System.out.println("2 " + sql_insert);
            con.executeUpdate(sql_insert);
        }catch(Exception err){
            System.out.println("Error getbillbylineno"+err.getMessage());
        }finally{
            try{
                //con.Close();
            }catch(Exception err){
                //
            }
        }
        //ResultSet rs =

    }
    public boolean ProcessJoinBill(String hospital_code, String startDate, String toDate){
        this.con = new DBConnection();
        this.con.connectToLocal();
        this.setStartDate(startDate);
        this.setToDate(toDate);
        this.hospital_code = hospital_code;
        boolean status = true;
        ResultSet rs = this.getLineNO();
        int count = 0;
        try{
            con.executeUpdate("UPDATE INT_HIS_BILL SET OLD_AMOUNT = AMOUNT_BEF_DISCOUNT " +
    	            "WHERE HOSPITAL_CODE = '"+hospital_code+"' " +
    	            "AND TRANSACTION_DATE BETWEEN '"+startDate+"' AND '"+toDate+"'");
            while(rs.next()){
                this.getBillByLineNO(rs.getString("LINE_NO"));
                count++;
            }
        }catch(Exception err){
            System.out.println("Error main join bill : "+err.getMessage());
            status = false;
        }finally{
            try {
                rs.close();
            } catch (SQLException ex) {
            }
        }
        return status;
    }

    /* test function
    public static void main(String[] arg){
        ProcessJoinBill j = new ProcessJoinBill();
        j.ProcessJoinBill("00001","20081201", "20090130");
    }
    */
    
}
